---
title: "Untitled"
output: html_document
date: "2023-04-14"
---
############################################
Title: 3_04_Figure_4.rmd
Purpose: This code creates Figure 4.
Last updated: 25-04-2023

Input:
    - Loan panel data. Latest version: "final_panel_loans.csv"
    - Compustat data. Latest version: "final_panel_compustat.csv"
    - Bond panel data. Latest version: "final_panel_bonds.csv"
    

Output:
    - Figure 4
    
##########################################



0. Setup *Set your file path here*
```{r, results="hide", include=FALSE}
#Clear user defined environment
rm(list=ls())

#Load packages
require(stargazer); require(lubridate); library(sandwich); library(ggplot2); require(dplyr); require(tidyverse); library(readxl); library(sandwich); library(lmtest); library(DescTools); library(fixest)

#options
options(digits=5)
options(stringsAsFactors = FALSE)
options(scipen=999)

#Assign dplyr verbs  
select <- dplyr::select
rename <- dplyr::rename
mutate <- dplyr::mutate
filter <- dplyr::filter
arrange <- dplyr::arrange
distinct <- dplyr::distinct
group_by <- dplyr::group_by
summarise <- dplyr::summarise
list <- base::list

#Define directories
global <- ".../2024_RFS_Replication_TOSUBMIT"

```


2. Setup data 
--------------------------------------------------------------

2.1 List of Bonds with GVKEY. 
```{r, results="hide", include=FALSE}

#Load
bond_level_gvkey <- read_csv(paste0(global, "/Data/final_panel_bonds.csv", ""), 
    col_types = cols(...1 = col_skip(), OFFERING_DATE = col_date(format = "%Y-%m-%d")))

bond_level_gvkey <- bond_level_gvkey %>% 
  select(cusip_id, PERMNO, PERMCO, conm_ccl, gvkey_ccl, OFFERING_DATE, fyear)

#Collapse to GVKEY-Year level
bond_list <- bond_level_gvkey %>% 
  arrange(gvkey_ccl, fyear) %>% 
  distinct(gvkey_ccl, fyear) %>% 
  mutate(bond_1st_issued = 1) %>% 
  distinct(gvkey_ccl, .keep_all = T)
  
```

2.2 COMPUSTAT data
```{r, results="hide", include=FALSE}

#Load
cmp <- read_csv(paste0(global, "/Data/final_panel_compustat.csv", ""), 
    col_types = cols(gvkey = col_number(), 
        datadate = col_date(format = "%d/%m/%Y")))


#Remove Non-US Firms
cmp <- cmp %>% 
  filter(loc == "USA")

#Remove financials
cmp <- cmp %>% 
  filter(!sic %in% c(6000:6999)) %>% 
  filter(!sic %in% c(9990:9999))

#Join bond issuance year
cmp <- left_join(cmp, bond_list, by=c("gvkey"="gvkey_ccl","fyear"="fyear"))

#Fill in bond dummy
cmp <- cmp %>% 
  group_by(gvkey) %>% 
  fill(bond_1st_issued, .direction = "down")


```

2.3 Check number of firms
```{r, results="hide", include=FALSE}

#Check how many gvkey's each year in COMPUSTAT
check <- cmp %>% 
  group_by(fyear) %>% 
  mutate(obs = 1) %>% 
  summarise(n_obs_total = sum(obs))

#Plot
ggplot(data = check) +
  geom_col(aes(x = fyear, y = n_obs_total)) +
  theme_bw()+
  ggtitle("Number of GVKEY's in COMPUSTAT")


#Check how many new gvkey's each quarter had bond outstanding issue a bond
check_b <- cmp %>%
  filter(bond_1st_issued == 1) %>% 
  mutate(obs = 1) %>% 
  group_by(fyear) %>% 
  summarise(n_firms_bond_issuer = sum(obs)) 

#Plot
ggplot(data = check_b) +
  geom_col(aes(x = fyear, y = n_firms_bond_issuer)) +
  theme_bw()+
  ggtitle("Number of GVKEY's in COMPUSTAT having ever issued a bond")


#Proportion of firms with bond
check <- left_join(check, check_b, by=c("fyear"="fyear"))
check <- check %>% 
  mutate(prop = n_firms_bond_issuer / n_obs_total)


check %>% 
  filter(fyear > 1999) %>% 
  ggplot() +
  geom_col(aes(x = fyear, y = prop)) +
  theme_bw()+
  ggtitle("Proportion of GVKEY's in COMPUSTAT having eveer issued a bond")

#PLot
g <- check %>% 
  filter(fyear > 1999) %>% 
  ggplot() +
  geom_col(aes(x = fyear, y = 1- prop)) +
  theme_bw()+
  ggtitle("Proportion Non-bond Firms") + 
  theme(plot.title = element_text(size = 10))  +
  xlab("")+
  ylab("")



```

2.4 Check how many bank firms appear in LSTA data
```{r, results="hide", include=FALSE}

#Check how many gvkey's each year in COMPUSTAT
check <- cmp %>% 
  group_by(fyear) %>% 
  mutate(obs = 1) %>% 
  summarise(n_obs_CMP_total = sum(obs))

#Check how many new gvkey's each quarter had bond outstanding issue a bond
check_b <- cmp %>%
  filter(bond_1st_issued == 1) %>% 
  mutate(obs = 1) %>% 
  group_by(fyear) %>% 
  summarise(n_firms_bond_issuer = sum(obs)) 

check_c <- cmp %>%
  filter(is.na(bond_1st_issued)) %>% 
  mutate(obs = 1) %>% 
  group_by(fyear) %>% 
  summarise(n_firms_bank_issuer = sum(obs)) 

check <- left_join(check, check_b, by=c("fyear"="fyear"))
check <- left_join(check, check_c, by=c("fyear"="fyear"))

#Number of Bank Firms each year
check <- check %>% 
  filter(fyear > 1998) 
rm(check_b, check_c)



#LSTA data
LSTA_monthly_ytm <- read_csv(paste0(global, "/Data/final_panel_loans.csv", ""), 
    col_types = cols(...1 = col_skip(), date_m = col_date(format = "%Y-%m-%d")))


#list of gvkeys of bank market firms
list_bank <- cmp %>%
  filter(is.na(bond_1st_issued)) %>% 
  group_by(fyear) %>% 
  distinct(gvkey)

#List of gvkeys in LSTA
list_lsta <- LSTA_monthly_ytm %>% 
  group_by(fyear) %>% 
  distinct(gvkey_comb) %>% 
  mutate(num_bankfirms_in_lsta = 1) 

list_bank <- left_join(list_bank, list_lsta, by=c("fyear"="fyear", "gvkey"="gvkey_comb"))


list_bank <- list_bank %>% 
  group_by(fyear) %>% 
  summarise(n_bankfirms_in_LSTA = sum(num_bankfirms_in_lsta, na.rm = T))


#Final compare
check <- left_join(check, list_bank, by=c("fyear"="fyear"))

check <- check %>% 
  mutate(n_bankfirms_not_in_lsta = n_firms_bank_issuer - n_bankfirms_in_LSTA)

```

2.5 What % of loan spread firms have a bond outstanding
```{r, results="hide", include=FALSE}

#LSTA data
LSTA_monthly_ytm <- read_csv(paste0(global, "/Data/final_panel_loans.csv", ""), 
    col_types = cols(...1 = col_skip(), date_m = col_date(format = "%Y-%m-%d")))

n_gvkey <- LSTA_monthly_ytm %>% 
  group_by(fyear) %>% 
  distinct(gvkey_comb) %>% 
  mutate(obs = 1) %>% 
  group_by(fyear) %>% 
  summarise(n_gvkeys_in_LSTA = sum(obs, na.rm = T))

#list of gvkeys in LSTA
n_list <- LSTA_monthly_ytm %>% 
  group_by(fyear) %>% 
  distinct(gvkey_comb)  

#list of gvkeys of bank market firms
n_bank <- cmp %>%
  filter(is.na(bond_1st_issued)) %>% 
  group_by(fyear) %>% 
  distinct(gvkey) %>% 
  mutate(n_bankfirm_in_cmo = 1) 

n_list <- left_join(n_list, n_bank, by = c("fyear"="fyear", "gvkey_comb"="gvkey"))

n_list <- n_list %>% 
  group_by(fyear) %>% 
  summarise(n_bankfirms_in_cmp = sum(n_bankfirm_in_cmo, na.rm = T))

n_gvkey <- left_join(n_gvkey, n_list, by=c("fyear"="fyear"))

```



3. Plot share of Bank and Bond Firms
------------------------------------------------

3.1 Gross PPE
```{r, results="hide", include=FALSE}

#Format date/market cap
#Start in 1985-12 to match ratings
cmp_ppe <- cmp %>% 
  filter(datadate > "1998-01-01")  %>% 
  select(gvkey, datadate, fyear, ppegt, bond_1st_issued)


cmp_ppe <- cmp_ppe %>% 
  group_by(fyear) %>% 
  mutate(ppegt_w = Winsorize(ppegt, na.rm = T, probs = c(0.05,0.95)))
  
  
summary(cmp_ppe$ppegt)  
summary(cmp_ppe$ppegt_w)  
 
#Dummy variable
cmp_ppe <- cmp_ppe %>% 
  mutate(bond_firm = ifelse(!is.na(bond_1st_issued),1,0),
         bank_firm = ifelse(is.na(bond_1st_issued),1,0))

#Total share
total <- cmp_ppe %>% 
  group_by(fyear) %>% 
  summarise(total = sum(ppegt_w, na.rm = T))

bank <- cmp_ppe %>% 
  filter(bank_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bank_firms = sum(ppegt_w, na.rm = T))

bond <- cmp_ppe %>% 
  filter(bond_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bond_firms = sum(ppegt_w, na.rm = T))

#Plot
total <- left_join(total, bank)
total <- left_join(total, bond)

total <- total %>% 
  mutate(prop_PPE_bank_firms = bank_firms/total,
         prop_PPE_bond_firms = bond_firms/total) %>% 
  filter(fyear > 1999)


b <- ggplot(data = total)+
  geom_col(aes(x = fyear, y = prop_PPE_bank_firms))+
  theme_bw()+
  ggtitle("Plant,Property,Equipment") + 
  theme(plot.title = element_text(size = 10))  +
  ylab("") +
  xlab("")

```

3.2 Total Assets
```{r, results="hide", include=FALSE}

#Format date/market cap
#Start in 1985-12 to match ratings
cmp_ta <- cmp %>% 
  filter(datadate > "1998-01-01")  %>% 
  select(gvkey, datadate, fyear, at, bond_1st_issued)

cmp_ta <- cmp_ta %>% 
  group_by(fyear) %>% 
  mutate(at_w = Winsorize(at, na.rm = T, probs = c(0.05,0.95)))

summary(cmp_ta$at)  
summary(cmp_ta$at_w)  

#Dummy variable
cmp_ta <- cmp_ta %>% 
  mutate(bond_firm = ifelse(!is.na(bond_1st_issued),1,0),
         bank_firm = ifelse(is.na(bond_1st_issued),1,0))

#Total share
total <- cmp_ta %>% 
  group_by(fyear) %>% 
  summarise(total = sum(at_w, na.rm = T))

bank <- cmp_ta %>% 
  filter(bank_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bank_firms = sum(at_w, na.rm = T))

bond <- cmp_ta %>% 
  filter(bond_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bond_firms = sum(at_w, na.rm = T))

#Plot
total <- left_join(total, bank)
total <- left_join(total, bond)

total <- total %>% 
  mutate(prop_at_bank_firms = bank_firms/total,
         prop_at_bond_firms = bond_firms/total) %>% 
  filter(fyear > 1998)


a <- ggplot(data = total)+
  geom_col(aes(x = fyear, y = prop_at_bank_firms))+
  theme_bw()+
  ggtitle("Total Assets") + 
  theme(plot.title = element_text(size = 10))  +
  ylab("") +
  xlab("")


```

3.3 Total Sales
```{r, results="hide", include=FALSE}
#Format date/market cap
#Start in 1985-12 to match ratings
cmp_s <- cmp %>% 
  filter(datadate > "1998-01-01")  %>% 
  select(gvkey, datadate, fyear, sale, bond_1st_issued) 

cmp_s <- cmp_s %>% 
  group_by(fyear) %>% 
  mutate(sale_w = Winsorize(sale, na.rm = T, probs = c(0.05,0.95)))

summary(cmp_s$sale)  
summary(cmp_s$sale_w)  


#Dummy variable
cmp_s <- cmp_s %>% 
  mutate(bond_firm = ifelse(!is.na(bond_1st_issued),1,0),
         bank_firm = ifelse(is.na(bond_1st_issued),1,0))

#Total share
total <- cmp_s %>% 
  group_by(fyear) %>% 
  summarise(total = sum(sale_w, na.rm = T))

bank <- cmp_s %>% 
  filter(bank_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bank_firms = sum(sale_w, na.rm = T))

bond <- cmp_s %>% 
  filter(bond_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bond_firms = sum(sale_w, na.rm = T))

#Plot
total <- left_join(total, bank)
total <- left_join(total, bond)

total <- total %>% 
  mutate(prop_sale_bank_firms = bank_firms/total,
         prop_sale_bond_firms = bond_firms/total) %>% 
  filter(fyear > 1998)

ggplot(data = total)+
  geom_col(aes(x = fyear, y = prop_sale_bond_firms))+
  theme_bw()+
  ggtitle("SALEQ Bond Firms / SALEQ All Firms", subtitle = "*Bond Firm is any firm with SP rating")

c <- ggplot(data = total)+
  geom_col(aes(x = fyear, y = prop_sale_bank_firms))+
  theme_bw()+
  ggtitle("Total Sales") + 
  theme(plot.title = element_text(size = 10))  +
  ylab("") +
  xlab("")

```

3.4 Total CAPX
```{r, results="hide", include=FALSE}
#Format date/market cap
#Start in 1985-12 to match ratings
cmp_capxy <- cmp %>% 
  filter(datadate > "1998-01-01")  %>% 
  select(gvkey, datadate, fyear, capx, bond_1st_issued)

cmp_capxy <- cmp_capxy %>% 
  group_by(fyear) %>% 
  mutate(capx_w = Winsorize(capx, na.rm = T, probs = c(0.05,0.95)))

summary(cmp_capxy$capx)  
summary(cmp_capxy$capx_w)  


#Dummy variable
cmp_capxy <- cmp_capxy %>% 
  mutate(bond_firm = ifelse(!is.na(bond_1st_issued),1,0),
         bank_firm = ifelse(is.na(bond_1st_issued),1,0))

#Total share
total <- cmp_capxy %>% 
  group_by(fyear) %>% 
  summarise(total = sum(capx_w, na.rm = T))

bank <- cmp_capxy %>% 
  filter(bank_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bank_firms = sum(capx_w, na.rm = T))

bond <- cmp_capxy %>% 
  filter(bond_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bond_firms = sum(capx_w, na.rm = T))

#Plot
total <- left_join(total, bank)
total <- left_join(total, bond)

total <- total %>% 
  mutate(prop_capx_bank_firms = bank_firms/total,
         prop_capx_bond_firms = bond_firms/total) %>% 
  filter(fyear > 1998)


d <- ggplot(data = total)+
  geom_col(aes(x = fyear, y = prop_capx_bank_firms))+
  theme_bw()+
  ggtitle("Capital Expenditure") + 
  theme(plot.title = element_text(size = 10))  +
  ylab("") +
  xlab("")

```

3.5 Total Employees
```{r, results="hide", include=FALSE}

#Format date/market cap
#Start in 1985-12 to match ratings
cmp_emp <- cmp %>% 
  filter(datadate > "1998-01-01")  %>% 
  select(gvkey, datadate, fyear, emp, bond_1st_issued)


cmp_emp <- cmp_emp %>% 
  group_by(fyear) %>% 
  mutate(emp_w = Winsorize(emp, na.rm = T, probs = c(0.05,0.95)))
  
  
summary(cmp_emp$emp)  
summary(cmp_emp$emp_w)  
 
#Dummy variable
cmp_emp <- cmp_emp %>% 
  mutate(bond_firm = ifelse(!is.na(bond_1st_issued),1,0),
         bank_firm = ifelse(is.na(bond_1st_issued),1,0))

#Total share
total <- cmp_emp %>% 
  group_by(fyear) %>% 
  summarise(total = sum(emp_w, na.rm = T))

bank <- cmp_emp %>% 
  filter(bank_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bank_firms = sum(emp_w, na.rm = T))

bond <- cmp_emp %>% 
  filter(bond_firm == 1) %>% 
  group_by(fyear) %>% 
  summarise(bond_firms = sum(emp_w, na.rm = T))

#Plot
total <- left_join(total, bank)
total <- left_join(total, bond)

total <- total %>% 
  mutate(prop_emp_bank_firms = bank_firms/total,
         prop_emp_bond_firms = bond_firms/total) %>% 
  filter(fyear > 1998)


e <- ggplot(data = total)+
  geom_col(aes(x = fyear, y = prop_emp_bank_firms))+
  theme_bw()+
  ggtitle("Employment") + 
  theme(plot.title = element_text(size = 10))  +
  ylab("") +
  xlab("")

```



4. Combine into Figure 4
---------------------------------

4.1 Figure 4
```{r, results="hide", include=FALSE}

library(ggpubr)

ggarrange(g,a,b,c,d,e, nrow = 3, ncol = 2)


```


